package dataGenerator;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class Generator{

	/**
	 * @param args
	 */
	
	private Connection connection = null;
	private String dbUrl = "jdbc:mysql://localhost/warehouse_bagtracking";
	static final int NIGHT = 6;
	static final int MORNING = 12;
	static final int AFTERNOON = 18;
	static final int EVENING = 23;
	
	
	
	public static void main(String[] args) {
		Generator g = new Generator();
		try {
			g.testDriver();
			g.connectToServer();
			g.generateTimeDimensionData();
			g.generateDateDimensionData();
			g.closeConnection();
			System.out.println("Bye-bye (wave)");
			
		}
		catch (SQLException e){
			System.err.println(e);
		}
		catch (ParseException p){
			System.err.println(p);
		}
		catch (Exception x){
			System.err.println(x);
		}
	}
	
	public void connectToServer() throws SQLException{
		if (connection == null){
			connection = DriverManager.getConnection(dbUrl, "root", "derpderpderp");
			System.out.println("***");
			System.out.println("Connection to server authorized!");
			System.out.println("***");
		}
	}
	
	public void generateTimeDimensionData() throws SQLException{
		if (connection != null){
			for (int h = 0; h < 24; h++){
				for (int m = 0; m < 60; m++){
					for (int s = 0; s < 60; s++){
						String sqlQuery = "";
						sqlQuery += "INSERT INTO `dimension_time` (`t_hour`, `t_minute`, `t_second`, `t_time_type`) ";
						sqlQuery += "VALUES (" + h + "," + m + ", " + s + ", '" + getTimeType(h) + "')";
						Statement st = connection.createStatement();
						st.execute(sqlQuery);
					}
				}
				int leftover = 24-(h+1);
				System.out.println("The data of " + h + " hour has just been loaded! " + leftover + " to go!");
			}
			System.out.println("***");
			System.out.println("The data was successfully loaded into table 'Dimension_time'!");
			System.out.println("***");
		}
		else {
			System.out.println("Connection to server was not successful");
		}
	}
	
	
	
	public String getTimeType(int hour){
		
		if (hour < NIGHT)
			return "night";
		if (hour < MORNING)
			return "morning";
		if (hour < AFTERNOON)
			return "afternoon";
		if (hour <= EVENING)
			return "evening";
		
		return "unknown";
	
	}
	
	public void generateDateDimensionData() throws Exception{
		if (connection != null){

			Calendar gcal = Calendar.getInstance();
		    SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");
		    Date start = sdf.parse("2011.06.01");
		    Date end = sdf.parse("2011.12.31");
		    gcal.setTime(start);
		    while (gcal.getTime().before(end)) {
		    	gcal.add(Calendar.DAY_OF_YEAR, 1);
		    	
		        String sqlQuery = "INSERT INTO `dimension_date` (`d_year`, `d_month`, `d_day`, `d_day_type`) ";
		        sqlQuery += "VALUES (" + gcal.get(Calendar.YEAR) + ", " + (gcal.get(Calendar.MONTH)+1) + ", " + gcal.get(Calendar.DAY_OF_MONTH) + ", ";
		        if ((gcal.get(Calendar.DAY_OF_WEEK) == 7) || (gcal.get(Calendar.DAY_OF_WEEK) == 1))
		        	sqlQuery += "'day-off')";
		        else
		        	sqlQuery += "'work day')";
		        Statement st = connection.createStatement();
				st.execute(sqlQuery);
		    }
		    System.out.println("***");
		    System.out.println("The data was successfully loaded into table 'Dimension_Date'!");
		    System.out.println("***");
		}
		else{
			System.out.println("Connection to server was not successful");
		}
		//
	}
	
	public void closeConnection() throws SQLException{
		if (connection != null){
			connection.close();
			System.out.println("***");
			System.out.println("Connection with server successfully closed!");
			System.out.println("***");
		}
	}
	
	public void testDriver() throws Exception {
		try{
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Driver found successfully. :)");
		}
		catch (ClassNotFoundException e){
			System.out.println("JDBC Driver for MySQL not found.");
			throw(e);
		}
	}

}